package dados;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.swing.JOptionPane;
import negocios.Familia;

public class ConectaBDFamilias {

	private Connection conexao;
	
	public ConectaBDFamilias() throws SQLException, ClassNotFoundException {
		
		this.conexao = ConectaBD.getConectacao();
		
	}
	
	public Object[] listarFamilias() {

         Object[] fam = null;

         try {

            PreparedStatement pst1 = conexao.prepareStatement("SELECT IFAMPROCODI, VFAMPRODESC, IFAMPROCOSE FROM tbfamilia ORDER BY IFAMPROCODI");
            PreparedStatement pst2 = conexao.prepareStatement("SELECT COUNT(*) as RESULT FROM tbfamilia");

            ResultSet rs1 = pst1.executeQuery();
            ResultSet rs2 = pst2.executeQuery();

            rs2.next();
            fam = new Familia[rs2.getInt("RESULT")];

            for(int cont = 0; cont < fam.length; cont++) {

                rs1.next();
                fam[cont] = new Familia(rs1.getInt(1), rs1.getString(2), rs1.getInt(3));

            }

            return fam;

        } catch(SQLException e) {

            JOptionPane.showMessageDialog(null, e.getMessage(), "Erro na execução da consulta", JOptionPane.ERROR_MESSAGE);

        }

		return fam;
		
	}
	
	public Familia consultarFamilia(Familia familia) {

        try {

            PreparedStatement pst = conexao.prepareStatement("SELECT IFAMPROCODI, VFAMPRODESC, IFAMPROCOSE FROM tbfamilia WHERE IFAMPROCODI = ?");
            
            pst.setInt(1, familia.getCodigo());

            ResultSet rs = pst.executeQuery();            
            
            if(rs.next()) {

                Familia fam = new Familia(rs.getInt(1), rs.getString(2), rs.getInt(3));

                pst.close();
                
                rs.close();

                conexao.close();

                return fam;

            }


        } catch(SQLException e) {

            JOptionPane.showMessageDialog(null, e.getMessage(), "Erro na execução da consulta", JOptionPane.ERROR_MESSAGE);

        } catch(NumberFormatException e) {

            JOptionPane.showMessageDialog(null, e.getMessage(), "Erro na execução da consulta", JOptionPane.ERROR_MESSAGE);

        }

        return null;
		
	}
	
	public void inserirFamilia(Familia familia) {
		
		try {

            PreparedStatement pst = conexao.prepareStatement("INSERT INTO tbfamilia (VFAMPRODESC, IFAMPROCOSE) VALUES (?, ?)");

            pst.setString(1, familia.getDescricao());
            pst.setInt(2, familia.getCodigoSequencial());

            pst.executeUpdate();
            pst.close();

            conexao.close();

        } catch(SQLException e) {

            JOptionPane.showMessageDialog(null, e.getMessage(), "Erro na execução da inserção", JOptionPane.ERROR_MESSAGE);

        }
		
	}
	
	public void editarFamilia(Familia familia) {
		
		if(this.consultarFamilia(familia) != null) {

            try {

                this.conexao = ConectaBD.getConectacao();

                PreparedStatement pst = conexao.prepareStatement("UPDATE tbfamilia SET VFAMPRODESC = ?, IFAMPROCOSE = ? WHERE IFAMPROCODI = ?");

                pst.setString(1, familia.getDescricao());
                pst.setInt(2, familia.getCodigoSequencial());
                pst.setInt(3, familia.getCodigo());

                pst.executeUpdate();
                pst.close();

                conexao.close();

            } catch(SQLException e) {

                JOptionPane.showMessageDialog(null, e.getMessage(), "Erro na execução da edição", JOptionPane.ERROR_MESSAGE);

            }

        }
		
	}
	
	public void excluirFamilia(Familia familia) {
		
		if(this.consultarFamilia(familia) != null) {

            try {

                this.conexao = ConectaBD.getConectacao();

                PreparedStatement pst = conexao.prepareStatement("DELETE FROM tbfamilia WHERE IFAMPROCODI = ?");

                pst.setInt(1, familia.getCodigo());

                pst.executeUpdate();
                pst.close();

                conexao.close();

            } catch(SQLException e) {

                JOptionPane.showMessageDialog(null, e.getMessage(), "Erro na execução da remoção", JOptionPane.ERROR_MESSAGE);

            }

        }
		
	}
	
}
